﻿using Dapper;
using Dapper.Contrib.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CaseManagerLibrary.DB.SQLite
{
    public class SQLiteHelper
    {
        public static object lock_conn = new object();

        private string fileName = "";
        public string FileName
        {
            get { return fileName; }
            set
            {
                fileName = value;
                ConnectionString = "data source=" + fileName;
            }
        }
        public string ConnectionString { get; set; }

        public bool Create(string file)
        {
            try
            {
                SQLiteConnection.CreateFile(file);
                return true;
            }
            catch (Exception ex)
            {
                string errorfile = System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "PSD", "LOG", "logerror.txt");

                var sw = System.IO.File.AppendText(errorfile);
                sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " 创建日志文件失败:" + ex.Message + (ex.InnerException == null? "" : ex.InnerException.Message));
                sw.Close();
                return false;
            }
        }

        public bool CreateTable<TEntity>(string file)
        {
            try
            {
                var entityType = typeof(TEntity);

                string tableName = entityType.Name;
                List<string> colNames = new List<string>();
                List<string> colTypes = new List<string>();
                string key = "PRIMARY KEY(\"{0}\")";
                string keyCol = "";

                foreach (var prop in entityType.GetProperties())
                {
                    colNames.Add(prop.Name);

                    if (prop.PropertyType == typeof(string) || prop.PropertyType == typeof(DateTime))
                    {
                        colTypes.Add("TEXT");
                    }
                    else if (prop.PropertyType == typeof(int))
                    {
                        colTypes.Add("INTEGER");
                    }
                    else
                    {
                        colTypes.Add("REAL");
                    }

                    if (prop.GetCustomAttributes(typeof(Dapper.Contrib.Extensions.KeyAttribute), true).Length > 0)
                    {
                        keyCol = prop.Name;
                    }
                }

                string sql = "CREATE TABLE IF NOT EXISTS " + tableName + "( ";

                for (int i = 0; i < colNames.Count; i++)
                {
                    sql += "\"" + colNames[i] + "\" " + colTypes[i] + ",";
                }

                if (string.IsNullOrEmpty(keyCol))
                {
                    sql = sql.Remove(sql.Length - 1, 1);
                }
                else
                {
                    sql += string.Format(key, keyCol);
                }

                sql += "  ) ";

                this.FileName = file;
                var conn = this.Open();

                if (conn != null)
                {
                    conn.Execute(sql);
                    this.Close(conn);

                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                string errorfile = System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "PSD", "LOG", "logerror.txt");

                var sw = System.IO.File.AppendText(errorfile);
                sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " 创建日志文件失败:" + ex.Message + (ex.InnerException == null ? "" : ex.InnerException.Message));
                sw.Close();
                return false;

                return false;
            }
        }

        public SQLiteConnection Open()
        {
            SQLiteConnection conn = null;
            try
            {
                conn = new SQLiteConnection(ConnectionString);
                conn.Open();
            }
            catch (Exception ex)
            {
                conn = null;
            }

            return conn;
        }

        public void Close(SQLiteConnection conn)
        {
            conn.Close();
            conn.Dispose();
        }

        public long Insert<TEntity>(SQLiteConnection conn, TEntity entity) where TEntity : class
        {
            return conn.InsertAsync<TEntity>(entity).ConfigureAwait(false).GetAwaiter().GetResult();
        }

        public bool Update<TEntity>(SQLiteConnection conn, TEntity entity) where TEntity : class
        {
            return conn.UpdateAsync<TEntity>(entity).ConfigureAwait(false).GetAwaiter().GetResult();
        }

        public bool Delete<TEntity>(SQLiteConnection conn, TEntity entity) where TEntity : class
        {
            return conn.DeleteAsync<TEntity>(entity).ConfigureAwait(false).GetAwaiter().GetResult();
        }

        public List<TEntity> Query<TEntity>(SQLiteConnection conn, string sql, object param = null)
        {
            return conn.QueryAsync<TEntity>(sql, param).ConfigureAwait(false).GetAwaiter().GetResult().ToList();
        }

        public int Execute(SQLiteConnection conn, string sql, object param = null)
        {
            return conn.ExecuteAsync(sql, param).ConfigureAwait(false).GetAwaiter().GetResult();
        }
    }
}
